﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.Infrastructure {
    public sealed class AcademicSpace : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The AcademicSpace's name field.
        /// </summary>
        private string name;

        /// <summary>
        /// The AcademicSpace's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }
        
        /// <summary>
        /// The AcademicSpace's name abbreviation field.
        /// </summary>
        private string abbreviation;

        /// <summary>
        /// The AcademicSpace's name abbreviation.
        /// </summary>
        public string Abbreviation {
            get {
                return this.abbreviation;
            }
            set {
                if ( this.abbreviation != value )
                    modifiedDBFields.Add( "abbreviation" );
                this.abbreviation = value;
            }
        }

        /// <summary>
        /// The AcademicSpace's capacity field.
        /// </summary>
        private int capacity;

        /// <summary>
        /// The AcademicSpace's capacity.
        /// </summary>
        public int Capacity {
            get {
                return this.capacity;
            }
            set {
                if ( this.capacity != value )
                    modifiedDBFields.Add( "capacity" );
                this.capacity = value;
            }
        }

        /// <summary>
        /// AcademicSpaceType field.
        /// </summary>
        private AcademicSpaceType spaceType;

        public AcademicSpaceType SpaceType {
            get {
                return this.spaceType;
            }
            set {
                if ( !this.spaceType.Equals( value ))
                    modifiedDBFields.Add( "academic_space_type_id" );
                this.spaceType = value;
            }
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return !( ( object ) this.name == null ) && ( this.name.Trim( ) != "" )
                    && ( !( ( object ) this.abbreviation == null ) && ( this.abbreviation.Trim( ) != "" ) )
                    && this.capacity > 0
                    && this.spaceType.IsValid;
            }
        }

         /// <summary>
        /// Gets a list of Services in the current Academic Spaces.
        /// </summary>
        public List<Service> Services {
            get {
                List<Service> servs = new List<Service>( );
                Database db = DatabaseFactory.CreateDatabase( );
                DbCommand dbCommand;
                string sqlCommand = "SELECT s.id, s.name, s.abbreviation, s.description FROM services AS s, academic_space_services as ass"
                    + " WHERE as.academic_space_services_id = @id AND ass.service_id = s.id ORDER BY s.name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    while ( rdr.Read( ) ) {
                        servs.Add( new Service( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "description" ] ) );
                    }
                    rdr.Close( );
                }
                return servs;
            }
        }
        #endregion

        #region Constructors
        public AcademicSpace ( ) {
            this.id = -1;
            this.name = "";
            this.abbreviation = "";
            this.capacity = 1;
            this.isNew = true;
            this.spaceType = new AcademicSpaceType( );
        }

        public AcademicSpace ( int id, string name, string abbreviation, int capacity, AcademicSpaceType spaceType ) {
            this.id = id;
            this.name = name;
            this.abbreviation = abbreviation;
            this.capacity = capacity;
            this.isNew = ( id == -1 );
            this.spaceType = spaceType;
        }

        public AcademicSpace ( string name, string abbreviation, int capacity, AcademicSpaceType spaceType )
            : this( -1, name, abbreviation, capacity, spaceType  ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            AcademicSpace a = obj as AcademicSpace;
            if ( ( object ) a == null ) {
                return false;
            }
            return ( this.name == a.name )
                && ( this.abbreviation == a.abbreviation )
                && ( this.capacity == a.capacity )
                && this.spaceType.Equals( a.spaceType );
        }

        /// <summary>
        /// Checks if the AcademicSpace is equal to the current instance.
        /// </summary>
        /// <param name="a">The AcademicSpace to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( AcademicSpace a ) {
            if ( ( object ) a == null ) {
                return false;
            }
            return ( this.name == a.name )
                && ( this.abbreviation == a.abbreviation )
                && ( this.capacity == a.capacity )
                && this.spaceType.Equals( a.spaceType );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.abbreviation.GetHashCode( )
                ^ this.capacity.GetHashCode( )
                ^ this.capacity.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.id, this.abbreviation, this.name, this.capacity, this.spaceType };
            bldr.AppendFormat( "[{0} :  {1}] : {2} - {4} : Capacity: {3}.", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ){
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                if ( this.spaceType.Save( ) ) {

                    sqlCommand = "SELECT count(*) = 0 as exists FROM academic_space WHERE id = @id OR name ILIKE @name";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    isNew = ( bool ) db.ExecuteScalar( dbCommand );
                    if ( isNew ) {
                        sqlCommand = "SELECT nextval('academic_space_id_seq')";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        this.id = ( int ) db.ExecuteScalar( dbCommand );

                        sqlCommand = "INSERT INTO academic_space ( id, name, abbreviation, capacity, academic_space_type_id )"
                           + " VALUES ( @id, @name, @abbreviation, @capacity )";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                        db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                        db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                        db.AddInParameter( dbCommand, "@capacity", DbType.String, this.capacity );
                        db.AddInParameter( dbCommand, "@academic_space_type_id", DbType.Int32, this.spaceType.Id );
                        saved = db.ExecuteNonQuery( dbCommand ) > 0;
                    }
                    else if ( this.modifiedDBFields.Count > 0 ) {
                        sqlCommand = "UPDATE academic_space SET name = @name, abbreviation = @abbreviaton,"
                            + " capacity = @capacity, academic_space_type_id = @academic_space_type_id"
                         + " WHERE id = @id";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                        db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                        db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                        db.AddInParameter( dbCommand, "@capacity", DbType.String, this.capacity );
                        db.AddInParameter( dbCommand, "@academic_space_type_id", DbType.Int32, this.spaceType.Id );
                        saved = db.ExecuteNonQuery( dbCommand ) > 0;
                    }
                    else {
                        //No update or insert needed
                        saved = true;
                    }
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the AcademicSpace: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM academic_space WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the AcademicSpace: " + e.Message );
            }
            return deleted;
        }

        #region Load Methods
        /// <summary>
        /// Loads the AcademicSpace with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The AcademicSpaces' id.</param>
        /// <returns>A AcademicSpace object with it's information or a null AcademicSpace object if the <code>id</code> was not present in the database.</returns>
        public static AcademicSpace Load ( int id ) {
            AcademicSpace b = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT a.id, a.name, a.abbreviation, a.capacity,"
                    + "a.academic_space_type_id, ast.name AS ast_name, ast.abbreviation AS ast_abbreviation, ast.description AS ast_description"
                    + " FROM academic_space AS a, academic_space_type AS ast"
                    + " WHERE a.id = @id"
                    + " AND a.academic_space_type_id = ast.id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        AcademicSpaceType t = new AcademicSpaceType( ( int ) rdr [ "academic_space_type_id" ], ( string ) rdr [ "ast_name" ],
                                ( string ) rdr [ "ast_abbreviation" ], rdr [ "ast_description" ] as string );
                        b = new AcademicSpace( id, ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( int ) rdr [ "capacity" ], t );
                        b.modifiedDBFields.Clear( );
                        b.ClearMessages( );
                        b.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                b.errorMessages.Add( string.Format( "There was an error loading the AcademicSpace with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return b;
        }

        /// <summary>
        /// Loads the AcademicSpace with the given <code>name</code>.
        /// </summary>
        /// <param name="id">The AcademicSpaces' name.</param>
        /// <returns>A AcademicSpace object with it's information or a null AcademicSpace object if the <code>name</code> was not present in the database.</returns>
        public static AcademicSpace LoadByName ( string name ) {
            AcademicSpace b = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT a.id, a.name, a.abbreviation, a.capacity,"
                    + "a.academic_space_type_id, ast.name AS ast_name, ast.abbreviation AS ast_abbreviation, ast.description AS ast_description"
                    + " FROM academic_space AS a, academic_space_type AS ast"
                    + " WHERE a.name ILIKE @name"
                    + " AND a.academic_space_type_id = ast.id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        AcademicSpaceType t = new AcademicSpaceType( ( int ) rdr [ "academic_space_type_id" ], ( string ) rdr [ "ast_name" ],
                                ( string ) rdr [ "ast_abbreviation" ], rdr [ "ast_description" ] as string );
                        b = new AcademicSpace( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( int ) rdr [ "capacity" ], t );
                        b.modifiedDBFields.Clear( );
                        b.ClearMessages( );
                        b.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                b.errorMessages.Add( string.Format( "There was an error loading the AcademicSpace {0}: {1}", name, e.Message ) );
            }
            return b;
        }

        /// <summary>
        /// Checks the database for existence of the AcademicSpace with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The AcademicSpaces' id.</param>
        /// <returns><code>True</code> if the academicSpace with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM academic_space WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for existence of the AcademicSpace with the given <code>name</code>.
        /// </summary>
        /// <param name="name">The AcademicSpaces' name</param>
        /// <returns><code>True</code> if the academicSpace with the given <code>name</code>exists, <code>false</code> otherwise.</returns>
        public static bool ExistsByName ( string name ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM academic_space WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Loads all of the AcademicSpaces in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the AcademicSpaces on the database.</returns>
        public static List<AcademicSpace> LoadAll ( ) {
            List<AcademicSpace> al = new List<AcademicSpace>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT a.id, a.name, a.abbreviation, a.capacity,"
                    + "a.academic_space_type_id, ast.name AS ast_name, ast.abbreviation AS ast_abbreviation, ast.description AS ast_description"
                    + " FROM academic_space AS a, academic_space_type AS ast"
                    + " WHERE a.academic_space_type_id = ast.id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                while ( rdr.Read( ) ) {
                    AcademicSpaceType t = new AcademicSpaceType( ( int ) rdr [ "academic_space_type_id" ], ( string ) rdr [ "ast_name" ],
                               ( string ) rdr [ "ast_abbreviation" ], rdr [ "ast_description" ] as string );
                    al.Add( new AcademicSpace( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( int ) rdr [ "capacity" ], t ) );
                }
                rdr.Close( );
            }
            return al;
        }
        #endregion

        #endregion
    }
}
